# Databricks notebook source
# MAGIC %md
# MAGIC ## TMR Populate Task Facts
# MAGIC <p><b>Description: </b>Add notebook description</p>
# MAGIC <b>Parent Process: </b>PL_FactPrism
# MAGIC <table class="table table-striped table-hover">
# MAGIC  <thead>
# MAGIC   <tr>
# MAGIC    <th>Contributor</th>
# MAGIC    <th>Date</th>
# MAGIC    <th>Version</th>
# MAGIC    <th>Comment</th>
# MAGIC    <th>WorkItem No</th>
# MAGIC   </tr>
# MAGIC  </thead>
# MAGIC  <tbody>
# MAGIC   <tr>
# MAGIC    <td>Iwan Dyke</td>
# MAGIC    <td>18/04/2024</td>
# MAGIC    <td>1.0</td>
# MAGIC    <td>Create initial release</td>
# MAGIC    <td></td>
# MAGIC   </tr>
# MAGIC  </tbody>
# MAGIC </table>

# COMMAND ----------

# MAGIC %md
# MAGIC ##Derive Dim_TaskSubjectCode FK
# MAGIC
# MAGIC The same logic is to be used in TMR.Dim_TaskSubject. Any changes need to be reflected there also.

# COMMAND ----------

# MAGIC %sql
# MAGIC -- Split up to first colon
# MAGIC CREATE OR REPLACE GLOBAL TEMPORARY VIEW StringSplit_Subject AS
# MAGIC (
# MAGIC   SELECT 
# MAGIC      f.`Task Instance ID`,
# MAGIC      TRIM(split_values.value) as value,
# MAGIC      ROW_NUMBER() OVER (PARTITION BY f.`Task Instance ID` ORDER BY f.`Task Instance ID`) AS RN
# MAGIC   FROM 
# MAGIC     databricks_poc.staging.tmr_raw f
# MAGIC   LATERAL VIEW explode(split(concat(f.`Task Subject(0-60)`, f.`Task Subject(61-120)`), ':')) split_values AS value
# MAGIC );
# MAGIC
# MAGIC -- Remove unwanted rows
# MAGIC CREATE OR REPLACE GLOBAL TEMPORARY VIEW StringSplit_Subject_II AS
# MAGIC (
# MAGIC   SELECT
# MAGIC     `Task Instance ID`
# MAGIC     ,`value`
# MAGIC     ,`RN` 
# MAGIC   FROM 
# MAGIC     global_temp.StringSplit_Subject
# MAGIC   WHERE RN = 1
# MAGIC );

# COMMAND ----------

# MAGIC %md
# MAGIC ##Populate TMR Task
# MAGIC

# COMMAND ----------

# MAGIC %sql
# MAGIC TRUNCATE TABLE databricks_poc.tmr.task;
# MAGIC
# MAGIC -- Final look up CTE used in populating TMR Task
# MAGIC WITH TaskSubject AS 
# MAGIC (
# MAGIC   SELECT
# MAGIC       `Task Instance ID`,
# MAGIC       CASE
# MAGIC           WHEN INSTR(`value`, 'NCS00') > 0 THEN SUBSTR(`value`, 1, (INSTR(`value`, 'NCS00') - 2))
# MAGIC           ELSE `value`
# MAGIC       END AS TaskSubject
# MAGIC   FROM global_temp.StringSplit_Subject_II
# MAGIC   WHERE `value` <> ''
# MAGIC   GROUP BY `Task Instance ID`, TaskSubject
# MAGIC )
# MAGIC --Populate TMR Task FACT
# MAGIC INSERT INTO databricks_poc.tmr.task
# MAGIC SELECT
# MAGIC   ff.`Task Instance ID` AS `TaskInstanceID`
# MAGIC   ,ff.`BPM Reference Number` AS `BPMReference`
# MAGIC   ,COALESCE(dim_bpmp.Code, -1) AS `Dim_BPM_Prefix`
# MAGIC   ,CAST(CASE
# MAGIC         WHEN TO_DATE(ff.`Date Task Created`, 'yyyyMMdd') IS NOT NULL THEN TO_DATE(ff.`Date Task Created`, 'yyyyMMdd')
# MAGIC         ELSE NULL
# MAGIC     END AS DATE) AS `CreateDate`
# MAGIC   ,CAST(CASE
# MAGIC         WHEN TO_DATE(ff.`Date Task Started`, 'yyyyMMdd') IS NOT NULL THEN TO_DATE(ff.`Date Task Started`, 'yyyyMMdd')
# MAGIC         ELSE NULL
# MAGIC     END AS DATE) AS `StartDate`
# MAGIC   ,CAST(CASE
# MAGIC         WHEN TO_DATE(ff.`Date Task Completed`, 'yyyyMMdd') IS NOT NULL THEN TO_DATE(ff.`Date Task Completed`, 'yyyyMMdd')
# MAGIC         ELSE NULL
# MAGIC     END AS DATE) AS `CompleteDate`
# MAGIC   ,COALESCE(dim_ts.Code, -1) AS `Dim_TaskStatusCode`
# MAGIC   ,COALESCE(dim_cj.Code, -1) AS `Dim_CaseJunkCode`
# MAGIC   ,COALESCE(dim_tsub.Code, -1) AS `Dim_TaskSubjectCode`
# MAGIC   ,COALESCE(dim_pr.Code, -1) AS `Dim_ProcessCode`
# MAGIC   ,COALESCE(dim_ps.Code, -1) AS `Dim_PrcoessStatusCode`
# MAGIC   ,COALESCE(dim_des.Code, -1) AS `Dim_DesignerCode`
# MAGIC   ,COALESCE(dim_lpa.Code, -1) AS `Dim_LPACode`
# MAGIC FROM
# MAGIC   databricks_poc.staging.tmr_raw ff
# MAGIC LEFT OUTER JOIN TaskSubject tsub
# MAGIC   ON ff.`Task Instance ID` = tsub.`Task Instance ID`
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_bpm_prefix dim_bpmp
# MAGIC   ON LEFT(ff.`BPM Reference Number`, 3) = dim_bpmp.Prefix
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_task_status dim_ts
# MAGIC   ON ff.`Task Status` = dim_ts.SourceValue
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_casejunk dim_cj
# MAGIC   ON ff.`BPM Reference Number` = dim_cj.BPMReference
# MAGIC   AND ff.`Applicant Company` = dim_cj.Company
# MAGIC   AND ff.`Applicant Name` = dim_cj.Applicant
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_process dim_pr
# MAGIC     ON SPLIT(`Current Sub Process Subject(0-60)`, ' : ')[1] = dim_pr.Description
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_designer dim_des
# MAGIC 	ON ff.`Designer First Name` = dim_des.FirstName
# MAGIC 	AND ff.`Designer Last Name` = dim_des.LastName
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_lpa dim_lpa
# MAGIC   ON ff.LPA = dim_lpa.Description
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_process_status dim_ps
# MAGIC   ON CASE
# MAGIC         WHEN ff.`Root Process Status` = 'CANCELED' THEN 'Cancelled'
# MAGIC         WHEN ff.`Root Process Status` = 'IN_PROGRESS' THEN 'In Progress'
# MAGIC         ELSE ff.`Root Process Status`
# MAGIC     END = dim_ps.Description
# MAGIC LEFT OUTER JOIN databricks_poc.tmr.dim_task_subject dim_tsub
# MAGIC   ON tsub.TaskSubject = dim_tsub.Description
# MAGIC WHERE
# MAGIC   ff.`BPM Reference Number` <> ''

# COMMAND ----------

# MAGIC %md
# MAGIC ##Exit and Cleanup

# COMMAND ----------

# MAGIC %sql
# MAGIC DROP TABLE IF EXISTS global_temp.StringSplit_Subject;
# MAGIC DROP TABLE IF EXISTS global_temp.StringSplit_Subject_II;
